using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;

namespace Data_Access_Tier
{
    public class DatabaseObjectBase
    {
        protected SqlConnection _dbconn;
        protected SqlDataAdapter _dbadapter;
        protected SqlTransaction _trans;
        protected DataSet _data;
        private static string connection_string = ConfigurationManager.AppSettings["ketnoi"].ToString();

        public DatabaseObjectBase()
        {
        }
        protected void Initialize(Boolean newConnection)
        {
            try
            {
                if (newConnection == true)
                {
                    _dbconn = new SqlConnection(connection_string); //TODO: Put your connection creation logic here.  Do not open the connection object; just create it.
                }
                _dbadapter = new SqlDataAdapter();
                _data = new DataSet();
            }
            catch (SqlException e)
            {
                throw new Exception("DatabaseObjectBase object Construction error:", e);
            }
        }
        protected void CloseConnection()
        {
            if (_dbconn.State == ConnectionState.Open)
                _dbconn.Close();
        }
        public Int32 GetDataSetCount(Int32 tableIndex)
        {
            if (_data != null)
                if (_data.Tables.Count > tableIndex)
                    return _data.Tables[tableIndex].Rows.Count;

            return 0;
        }
        public Int32 GetDataSetCount()
        {
            return GetDataSetCount(0);
        }

        public DataSet read_table(string pcaulenh)
        {
            try
            {
                _dbadapter = new SqlDataAdapter(pcaulenh, _dbconn);
                _dbadapter.Fill(_data);
                return _data;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            
        }
        public string Generate_ID(string TableName, string CodeColumnName, string FirstChars)
        {
            string id;
            string idmoi;

            DataSet ds= read_table("select top 1 " + CodeColumnName + " from " + TableName + " order by " + CodeColumnName + " desc");
            DataTable dt = ds.Tables[0];

            if (dt.Rows.Count > 0)
            {
                id = ds.Tables[0].Rows[0][CodeColumnName].ToString();
                String chuoi = id.Substring(2);
                int so = int.Parse(chuoi);

                if (so >= 0 && so < 9)
                    idmoi = FirstChars + "00000" + (so + 1).ToString();
                else if (so >= 9 && so < 99)
                    idmoi = FirstChars + "0000" + (so + 1).ToString();
                else if (so >= 99 && so < 999)
                    idmoi = FirstChars + "000" + (so + 1).ToString();
                else if (so >= 999 && so < 9999)
                    idmoi = FirstChars + "00" + (so + 1).ToString();
                else if (so >= 9999 && so < 99999)
                    idmoi = FirstChars + "0" + (so + 1).ToString();
                else
                    idmoi = FirstChars + (so + 1).ToString();
            }
            else
            {
                idmoi = FirstChars + "000001";

            }
            return idmoi;
        }
        public string Generate_ID_1_Char(string TableName, string CodeColumnName, string FirstChars)
        {
            string id;
            string idmoi;

            DataSet ds = read_table("select top 1 " + CodeColumnName + " from " + TableName + " order by " + CodeColumnName + " desc");
            DataTable dt = ds.Tables[0];

            if (dt.Rows.Count > 0)
            {
                id = ds.Tables[0].Rows[0][CodeColumnName].ToString();
                String chuoi = id.Substring(1);
                int so = int.Parse(chuoi);

                if (so >= 0 && so < 9)
                    idmoi = FirstChars + "00000" + (so + 1).ToString();
                else if (so >= 9 && so < 99)
                    idmoi = FirstChars + "0000" + (so + 1).ToString();
                else if (so >= 99 && so < 999)
                    idmoi = FirstChars + "000" + (so + 1).ToString();
                else if (so >= 999 && so < 9999)
                    idmoi = FirstChars + "00" + (so + 1).ToString();
                else if (so >= 9999 && so < 99999)
                    idmoi = FirstChars + "0" + (so + 1).ToString();
                else
                    idmoi = FirstChars + (so + 1).ToString();
            }
            else
            {
                idmoi = FirstChars + "000001";

            }
            return idmoi;
        }
        public int Generate_ID_Number(string TableName, string CodeColumnName)
        {
            int id;
            int idmoi;

            DataSet ds = read_table("select top 1 " + CodeColumnName + " from " + TableName + " order by " + CodeColumnName + " desc");
            DataTable dt = ds.Tables[0];

            if (dt.Rows.Count > 0)
            {
                id = int.Parse(ds.Tables[0].Rows[0][CodeColumnName].ToString());
                idmoi= id+1;
            }
            else
            {
                idmoi = 1;

            }
            return idmoi;
        }
        public int Generate_ID_Number_For_Salary_Step(string RankCode)
        {
            int id;
            int idmoi;

            DataSet ds = read_table("select top 1 StepCode from DIC_SALARY_STEP where RankCode='"+RankCode+"' order by StepCode desc");
            DataTable dt = ds.Tables[0];

            if (dt.Rows.Count > 0)
            {
                id = int.Parse(ds.Tables[0].Rows[0]["StepCode"].ToString());
                idmoi = id + 1;
            }
            else
            {
                idmoi = 1;

            }
            return idmoi;
        }
       
    }
}
